package haupt.dev.DatabaseConnection;



import haupt.dev.Config.ConfigReader;
import info.neocron.service.PublicInterface.Clan;
import info.neocron.service.PublicInterface.ExtendedOutpost;
import info.neocron.service.PublicInterface.ServerStatistic;

import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.GregorianCalendar;
import java.util.List;
import javax.xml.datatype.XMLGregorianCalendar;

import org.pmw.tinylog.Logger;


public class MySQLConnection {
	
    private String URL;
    private ConfigReader CFGreader;
    private java.sql.Connection connection;
    
	public MySQLConnection(ConfigReader CFGreader) {
		this.CFGreader = CFGreader;
		URL = "jdbc:mysql://" + CFGreader.getProperty("host")
				+ ":" + CFGreader.getProperty("port")
				+ "/" + CFGreader.getProperty("database");
		connect();
		createTables();
	}
	
    /* #################################
     * ###     CREATE ALL TABLES     ###
     * #################################
     */
    private void createTables() {
    	try {
    		DatabaseMetaData dmd = connection.getMetaData();
    		ResultSet set;
    		
    		set = dmd.getTables(null, null, "NC_SERVERS", null);
        	if(!set.next()) {
				createNC_SERVERSTable();
			}
        	
        	set = dmd.getTables(null, null, "NC_OUTPOSTS", null);
        	if(!set.next()) {
        		createNC_OUTPOSTSTable();
        	}
		} catch (SQLException e) {
			Logger.error(e);
		}
	}

    /* #################################
     * ###   CONNECT WITH DATABASE   ###
     * #################################
     */
	public void connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(URL,
            		CFGreader.getProperty("user"), CFGreader.getProperty("password"));
        } catch (Exception e) {
            Logger.warn("Can't connect with user: {0} and password: {1}",
            		CFGreader.getProperty("user"),
            		CFGreader.getProperty("password"));
        }
    }
	
    /* ######################################
     * ### CHECK FOR AVAILABLE CONNECTION ###
     * ######################################
     */
    public boolean isConnected() {
        try {
        	ResultSet rs = ReturnQuery("SELECT 1;");
            if (rs == null) {
                return false;
            }
            if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
				Logger.error(e);
		}
        return false;
    }
    
    public void close() {
        if (this.connection != null) {
            try {
                this.connection.close();
            } catch (Exception e) {
            }
        }
    }
    
    
    /* #################################
     * ###   SQL QUERY WITH RESULT   ###
     * #################################
     */  
    public ResultSet ReturnQuery(String query) {
        try {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            return rs;
        } catch (SQLException e) {
            Logger.error(e);
        }
		return null;
    }
    
    
    /* #################################
     * ###     STATEMENT TO SQL      ###
     * #################################
     */
    public boolean RunQuery(String query) {
            Statement stmt;
			try {
				stmt = connection.createStatement();
				return stmt.execute(query);
			} catch (SQLException e) {
				Logger.error(e);
			}
			return false;
    }
    
    /* #################################
     * ###   CALENDAR TO TIMESTAMP   ###
     * #################################
     */
	private Timestamp getTimestamp(XMLGregorianCalendar conquerTime) {
		GregorianCalendar gc = conquerTime.toGregorianCalendar();
		long timeInMillis = gc.getTimeInMillis();
		return new Timestamp(timeInMillis);
	}

	
    /* #################################
     * ### CREATE TABLES IN DATABASE ###
     * #################################
     */
	private void createNC_OUTPOSTSTable() {
		this.RunQuery("CREATE TABLE NC_OUTPOSTS"
				+ "(OUTPOSTID INT NOT NULL,"
				+ " SECTOR VARCHAR(50),"
				+ " OPGAINED TIMESTAMP,"
				+ " SECURITY INT,"
				+ " CLANSHORT VARCHAR(50),"
				+ " CLANNAME VARCHAR(50),"
				+ " FACTIONNAME VARCHAR(50),"
				+ " PRIMARY KEY(OUTPOSTID))");
	}

	private void createNC_SERVERSTable() {
		this.RunQuery("CREATE TABLE NC_SERVERS"
				+ "(ID SMALLINT NOT NULL," 
				+ " SERVERNAME VARCHAR(6) NOT NULL,"
				+ " STATUS VARCHAR(7),"
				+ " PLAYERS INT(6),"
				+ " LATENCY INT(6),"
				+ " PRIMARY KEY(ID))");
	}  
    
	
    /* #######################################
     * ###   STORE SERVERDATA TO DATABASE  ###
     * #######################################
     */
	public void storeServerData(List<ServerStatistic> serverStatistics) {
		if(serverStatistics == null || serverStatistics.isEmpty()) {
			return;
		}
		
		for(ServerStatistic s : serverStatistics) {
			this.RunQuery(
					"INSERT INTO NC_SERVERS(ID, SERVERNAME, STATUS, PLAYERS, LATENCY)" +
					"VALUES ("
						+ s.getId() + ",'"
						+ s.getName() + "','"
						+ s.isIsOnline() + "',"
						+ s.getPlayers() + ","
						+ s.getLatency() + ") " + 
					"ON DUPLICATE KEY UPDATE "
						+ "SERVERNAME='" + s.getName() + "',"
						+ "STATUS='" + s.isIsOnline() + "', "
						+ "PLAYERS=" + s.getPlayers() + ", "
						+ "LATENCY=" + s.getLatency());
		}
	}

    /* #####################################
     * ### STORE OUTPOSTDATA TO DATABASE ###
     * #####################################
     */
	public void storeOutpostData(List<ExtendedOutpost> outpostList) {
		if(outpostList == null || outpostList.isEmpty()) {
			return;
		}

		for(ExtendedOutpost eo : outpostList) {
			Timestamp conquerTime = getTimestamp(eo.getConquerTime());
			Clan clan = eo.getClan();
			
			this.RunQuery(
					"INSERT INTO NC_OUTPOSTS(OUTPOSTID, SECTOR, OPGAINED, SECURITY, CLANSHORT, CLANNAME, FACTIONNAME)" +
					"VALUES (" 
							+ eo.getId() + ",'"
							+ eo.getSector() + "','"
							+ conquerTime + "',"
							+ eo.getSecuritySetting() + ",'"
							+ clan.getShortName() + "','"
							+ clan.getName() + "','"
							+ eo.getFaction() + "') " +
					"ON DUPLICATE KEY UPDATE "
							+ "CLANSHORT='" + clan.getShortName() + "',"
							+ "CLANNAME='" + clan.getName() + "',"
							+ "SECURITY=" + eo.getSecuritySetting() + ","
							+ "FACTIONNAME='" + eo.getFaction() + "',"
							+ "OPGAINED='" + conquerTime + "'");
		}
	}
}
